With the technological advances in database management systems (DBMSs), there is an ongoing and increasing need to increase performance efficiency. Specifically, there is an ongoing and increasing need to increase database performance as related to triggers.
Generally, in a DBMS the current version of data is stored in a “base” table. Each row in the base table can be changed as the result of a delete, insert or update operation. When a row is changed, the change can be stored in a set of temporary “transition” tables. Such a set of transition tables can be automatically created and managed by the DBMS. The set of transition tables may include a “delete” table, which stores rows deleted from the base table, and an “insert” table, which stores rows inserted into the base table. If a row is updated, the previous version of the row is stored in the delete table, and the current version of the row is stored in the insert table.
The transition tables enable the DBMS to test the effects of changes and to set conditions for trigger actions. Specifically, transition tables extend referential integrity between tables, change data in a base table underlying a particular view, check for errors and take action based on a detected error, and find the difference between the state of a table before and after a change and take action based on the difference.
A trigger is a special type of stored procedure that is executed when an insert, update or delete statement modifies one or more rows in a table. Because a DBMS (e.g., SQL Server) can call a trigger for every specified operation on a particular table, one can use triggers to extend the built-in integrity and data manipulation features of the DBMS. Traditionally, trigger, also referred to as after-trigger, implementations were executed by conducting a scan of the transaction log to retrieve old and new images of records. However, traditional log based implementations of after-triggers presented a number of problems. For example, the log inherently created a processing bottleneck. Although, a log approach may be sufficient in some sequential write applications, it is not particularly useful in random read applications. This bottleneck characteristic is at least in part attributed to the lack of scalability of the log scan after-trigger implementation.
Another pitfall of the conventional log scan approach is that the log is not buffer pool backed. As will be appreciated, reading from an inserted/deleted table likely involves a large amount of input/output (I/O) processes. Finally, large object (LOB) database field access is not supported in after-trigger through conventional log based implementations. Due to these limitations of the traditional log scan approach, recent efforts and developments have been directed to versioning-based implementations. Existing versioning-based after-trigger implementations rely on versioning to provide old and new images of records in a delta worktable. Accordingly, a delta worktable is maintained which stores key values of records which have been inserted, deleted or updated to identify records in the inserted/deleted tables.
Although these new implementations solved many of the problems of conventional log based approach, they are much slower than the log based approach. This deficiency stems from the cost associated with populating the delta worktable during a data manipulation language (DML) event as well as the cost to construct inserted/deleted tables during trigger body execution by joining the delta worktable and base table. Together this translates into one bookmark lookup plus a versioning store visit in the base table per row scanned in the delta worktable.
Even though attempts have been made via version-based implementations to reduce and/or mitigate the aforementioned concerns and to increase trigger efficiency, there is still a substantial unmet need for a system and/or methodology to employ a versioning-based implementation to create a more cost effective and efficient versioning-based after-trigger implementation.